﻿using Dapper;

using HBase;
using HBase.db;
using hCommand;
using Net.http;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using static Dapper.SqlMapper;

namespace Persistence {
    public class AbsStatServer : AbsExtendsServer, IStatServer {
       

        public void statDoi() { 
           List<WhDoiDet> doiDets= opConn().Query<WhDoiDet>("select * from wh_doi_det where cv>0").ToList();
            foreach (WhDoiDet det in doiDets) {
                String sumSql = "select IFNULL( Sum(count_DB),0) from (select count_DB from core_stock_det  where doi_det_id=@id  and status={0} UNION ALL select count_DB from d_Stock_Det1 where doi_det_id=@id) a";
                double sum= opConn().QueryUnique<double>(HUtil.s(sumSql, StockDetailStatus.库存), det);
                sum = CsdUtil.Round(sum);
                String sql = "update wh_doi_det set cv=0,finsh_count= @sum  where id=@id and cv=@cv";
                int c = opConn().Execute(HUtil.s(sql, StockDetailStatus.库存), new { sum, det.id, det.cv });
                if (c > 0) { 
                    String countSql = "SELECT count(*) FROM  wh_doi_det n    where n.pid=@pid  and n.plan_Count-n.finsh_Count>" + CsdUtil.loss;
                    int woringDet = opConn().QueryUnique<int>(countSql, det);
                    if (woringDet == 0) {
                        opConn().Execute("update wh_doi set status=9,finsh_time=sgj_get_sys_now where id=(select pid from wh_doi_det where id=@id)", det);
                    }
                }
            }
        }

        public void statDoo() {
            List<WhDooDet> dooDets = opConn().Query<WhDooDet>("select * from wh_doo_det where cv>0").ToList();
            foreach (WhDooDet det in dooDets) {
                String sql = "update wh_doo_det set finsh_count=(select IFNULL( Sum(count_DB),0) from d_Stock_Det1  where doo_det_id=@id  ),cv=0 where id=@id and cv=@cv";
                int c = opConn().Execute(sql, det);
                if (c > 0) {
                    String countSql = "SELECT count(*) FROM wh_doo_det n   where n.pid=@pid  and n.plan_Count-n.finsh_Count>" + CsdUtil.loss;
                    int woringDet = opConn().QueryUnique<int>(countSql, det);
                    if (woringDet == 0) {
                        opConn().Execute("update wh_doo set status=9,finsh_time=sgj_get_sys_now where id=(select pid from wh_doo_det where id=@id)", det);
                    }
                }
            }
          
        }
        public void statBatch() { 
            List<WhBatch> batchs = opConn().Query<WhBatch>("select * from wh_batch where cv>0").ToList();
            foreach (WhBatch batch in batchs) {

                String countSql = "select sum(count_db) from core_stock_det  where  batch_No=@batchNo  and item_Id=@itemId and status<{0}  and status>{1}";
                countSql = HUtil.s(countSql, StockDetailStatus.上限, StockDetailStatus.下线);
                double count = opConn().QueryUnique<double>(countSql, batch);
             
                if (count > 0) {
                    batch.count = count;
                    String updateSql = "update Wh_Batch set count=@count,cv=0 where id=@id and cv=@cv";
                    opConn().Execute(HUtil.s(updateSql, StockDetailStatus.库存), batch);
                } else {
                    opConn().Execute("delete from  Wh_Batch  where id=@id", batch);
                }
                opConn().Execute("update core_item set cv=cv+1 where id = @itemId", batch);
            }
        }
        public void statItme() {
            List<CoreItem> items = opConn().Query<CoreItem>("select * from core_item where cv>0").ToList();
            foreach (CoreItem item in items) {
                String sql = "update Core_Item set count=(select IFNULL(  sum(count),0) from wh_batch  where  item_id=@id ) ,cv=0 where id=@id and cv=@cv";
                int c = opConn().Execute(sql, item);
            }
        }

        public void statDailyData( ) {
          
            var n = DateTime.Now;  
            doStat(new DateTime(n.Year, n.Month, n.Day)); 
        }
        public void statHisDailyData() {
            var n = DateTime.Now;
            if (n.Hour < 2) {
                return;
            }
            BParam p = opConn().GetById<BParam>(999); 
            DateTime b;
            if (p.v < 20250101) { 
                b = new DateTime(n.Year, n.Month, n.Day).AddDays(-1); 
            } else {
                int t = (int)p.v;
                b = toD((int)p.v).AddDays(1);
                if (getV(b) >= getV(n)) {
                    return;
                }
            }
            doStat(b);
            p.v = getV(b); 
            opConn().Execute("update B_Param set v=@v where id=@id", p);

        }

        int getV(DateTime d) {
           int dd = d.Year * 10000 + d.Month * 100 +d.Day;
            return dd;
        }
        DateTime toD(int  t) {
           var  d = new DateTime((int)(t / 10000), (int)(t % 10000) / 100, (int)(t % 100));
            return d;
        }


        List<WhDoStat> statIn(StatDate obj) { 
            String qsql = "   SELECT count_db ,item_Id ,batch_no,unit FROM {0}     where create_Time>=@b  and create_Time<@e and status>" + (int)StockDetailStatus.待绑定;
            String unionSql = String.Format(qsql, "core_stock_det") +"    union all   "+ String.Format(qsql, "d_stock_det1") ;
            if (obj.allHis) {
                unionSql+= "  union all   " + String.Format(qsql, "d_stock_det2");
            }
            String statSql = $"SELECT sum(count_db) in_Count,item_Id ,batch_no,unit from ({unionSql}) a GROUP BY  item_Id,batch_no,unit";
            FileLogger.Error(statSql, null);
            List<WhDoStat> inStat = opConn().Query<WhDoStat>(statSql, obj).ToList();
            return inStat;
        }
        List<WhDoStat> statOut(StatDate obj) {
            String qsql = "   SELECT  count_db ,item_Id ,batch_no,unit FROM {0}     where out_Time>=@b  and out_Time<@e  ";
            String unionSql= String.Format(qsql, " d_stock_det1 ");
            if (obj.allHis) {
                unionSql += "    union all   " + String.Format(qsql, "d_stock_det2");
            }
            String statSql = $"SELECT sum(count_db) out_Count,item_Id ,batch_no,unit from ({unionSql}) a GROUP BY  item_Id,batch_no,unit";
            FileLogger.Error(statSql, null);
            List<WhDoStat> outStat = opConn().Query<WhDoStat>(statSql, obj).ToList();
            return outStat;
        }
        void doStat(DateTime b) {
            var betw = new StatDate( b);
            FileLogger.Error(b.ToString("yyyy-MM-dd"), null);
            List<WhDoStat> inStat = statIn(betw);
             List<WhDoStat> outStat = statOut(betw);
            foreach (WhDoStat os in outStat) {
                checkOut(os, inStat);
            }
            
            opConn().Execute("delete  from Wh_Do_Stat where create_Time= @b", betw); 
            foreach (WhDoStat os in inStat) {
                String sqlDate = "SELECT  max(create_time) FROM `wh_do_stat`  where item_id=@itemId and batch_No =@batchNo and create_Time<@b";
                DateTime? date = opConn().QueryUnique<DateTime>(sqlDate, new { os.itemId,os.batchNo,b });
                double r = 0;
                if (date!=null) {
                    FileLogger.Error(date.ToString(), null);
                    String sql11 = "select remain from wh_do_stat   where   create_time=@date and item_id=@itemId  and batch_No =@batchNo ";
                    r = opConn().QueryUnique<double>(sql11, new { os.itemId, os.batchNo, date }); 
                } 
                os.remain = r + os.inCount - os.outCount; 
            } 
            if (inStat.Count > 0) {
                for (int i = 0; i < inStat.Count; i++) {
                    inStat[i].createTime = b;
                }
                opConn().BatchInsert(inStat); 
            }
        } 
        void checkOut(WhDoStat os, List<WhDoStat> inStat) {
            for (int i = 0; i < inStat.Count; i++) {
                if (inStat[i].itemId == os.itemId&& inStat[i].batchNo== os.batchNo&& inStat[i].unit == os.unit) {
                    inStat[i].outCount = os.outCount;
                    return;
                }
            }
            inStat.Add(os);
        }

        
       


    }
    public class StatDate {
        public DateTime b { get; set; }
        public DateTime e { get; set; }
        public bool allHis = false;
        public StatDate(DateTime d) {
            b = d;
            e = d.AddDays(1);
            allHis = (DateTime.Now.Ticks - b.Ticks) > 10000L * 1000 * 24 * 3600 * 20;


        }
    }   
}
